﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;

namespace ChinaDynastyTours.Controls
{
    class VehicleEvaluateCtrl
    {
        /// <summary>
        /// 为获取团行程计划中的相关信息
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        public static DataSet GetTravelOrderForVehicleEval(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT ProviderID as TravelAgencyID,ProviderName as TravelAgencyName,City FROM fee_TravelOrder WHERE TourgroupID='{0}'", tourGroupID);

            return db.ExecuteDataSet(CommandType.Text, sql);
        }

        public static string CreateVehicleEvaluateData(VehicleEvaluateEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO eval_VehicleEvaluate ([TourgroupID], [TourgroupName], [ProviderID], [ProviderName], [CityName], [Good], [Middle], [Common], [Bad],  [Praise], [Complain],[Remark])
                                         VALUES('{0}', '{1}',{2},'{3}','{4}',{5},{6},{7},{8},{9},{10},'{11}');Select SCOPE_IDENTITY();",
                                          entity.TourgroupID,
                                          entity.TourgroupName,
                                          entity.ProviderID,
                                          entity.ProviderName,
                                          entity.CityName,
                                          entity.Good,
                                          entity.Middle,
                                          entity.Common,
                                          entity.Bad,
                                          entity.Praise,
                                          entity.Complain,
                                          entity.Remark);

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        public static DataTable GetVehicleEvaluateData(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT * FROM eval_VehicleEvaluate WHERE TourGroupID='{0}' ORDER BY EvaluateID", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        internal static int UpdateVehicleEvaluateData(VehicleEvaluateEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE eval_VehicleEvaluate SET [TourgroupID]='{0}', [TourgroupName]='{1}', 
                                                                  [ProviderID]={2}, [ProviderName]='{3}', [CityName]='{4}', [Good]={5}, [Middle]={6}, 
                                                                  [Common]={7}, [Bad]={8},[Praise]={9},[Complain]={10},[Remark]='{11}'  
                                                                  WHERE [EvaluateID]={12}",
                                                                  entity.TourgroupID,
                                                                  entity.TourgroupName,
                                                                  entity.ProviderID,
                                                                  entity.ProviderName,
                                                                  entity.CityName,
                                                                  entity.Good,
                                                                  entity.Middle,
                                                                  entity.Common,
                                                                  entity.Bad,
                                                                  entity.Praise,
                                                                  entity.Complain,
                                                                  entity.Remark,
                                                                  entity.EvaluateID);

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }
        public static DataTable GetVehicleEvalAnalysis(string cityname, string startTime, string endTime)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT r.CityName,r.ProviderName,r.ProviderID,   (ROUND(CONVERT(float,sum(r.Good))/SUM(r.Good+r.Middle+r.Common+r.Bad),2)) as Good ,
									                                             (ROUND(CONVERT(float,sum(r.Middle))/sum(r.Good+r.Middle+r.Common+r.Bad),2)) as Middle,
									                                             (ROUND(CONVERT(float,sum(r.Common))/sum(r.Good+r.Middle+r.Common+r.Bad),2)) as Common,
									                                             (ROUND(CONVERT(float,sum(r.Bad))/sum(r.Good+r.Middle+r.Common+r.Bad),2)) as Bad,
									                                             sum(r.Praise) as Praise,
									                                             sum(r.Complain) as Complain 
                                        FROM eval_VehicleEvaluate r,orgt_TourGroup t 
                                        WHERE r.CityName like '%{0}%' and r.TourgroupID=t.TourgroupID and t.StartDate BETWEEN '{1}' AND '{2}' 
                                        and (r.Good+r.Middle+r.Common+r.Bad)>0
                                        GROUP BY r.CityName,r.ProviderName,r.ProviderID", cityname, startTime, endTime);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable GetVehicleEvalAnalysisDetail(string providerID, string startTime, string endTime)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"select  t.tourgroupid,t.tourgroupname, r.CityName,r.ProviderName, r.Good,r.Middle,r.Common,r.Bad,r.Praise,r.Complain from eval_VehicleEvaluate r,orgt_TourGroup t where r.TourgroupID=t.TourgroupID and t.StartDate BETWEEN '{0}' AND '{1}'  and r.ProviderID={2}", startTime, endTime, providerID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
    }
}
